package com.lin.tally.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.lin.tally.utils.FloatUtils;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * 负责管理数据库的类
 * 主要对于表当中的内容进行操作，增删改查
 */
public class DBManager {

    private static SQLiteDatabase db;
    //初始化数据对象
    public static void initDB(Context context){
        DBOpenHelper helper=new DBOpenHelper(context);//得到帮助类对象
        db=helper.getWritableDatabase();//得到数据库对象
    }

    /**
     * 读取数据库中的数据，写入内存集合里面
     * kind:表示收入或者支出
     */
    public static List<TypeBean> getTypeList(int kind){
        List<TypeBean> list=new ArrayList<>();
        //读取typetb表中的数据
        String sql="select * from typetb where kind='"+kind+"'";
        Cursor cursor=db.rawQuery(sql,null);
        //循环读取游标内容，存储到对象当中
        while (cursor.moveToNext()) {
           String typename= cursor.getString(cursor.getColumnIndex("typename"));
           int imageId= cursor.getInt(cursor.getColumnIndex("imageId"));
           int sImageId= cursor.getInt(cursor.getColumnIndex("sImageId"));
           int kind1= cursor.getInt(cursor.getColumnIndex("kind"));
           int id= cursor.getInt(cursor.getColumnIndex("id"));
           TypeBean typeBean=new TypeBean(id,typename,imageId,sImageId,kind1);
           list.add(typeBean);
        }
        return list;
    }

    /**
     * 向记账表当中插入一条元素
     */
    public static void insertItemToAccounttb(AccoountBean bean){
        ContentValues values=new ContentValues();
        values.put("typename",bean.getTypename());
        values.put("sImageId",bean.getsImageId());
        values.put("beizhu",bean.getBeizhu());
        values.put("money",bean.getMoney());
        values.put("time",bean.getTime());
        values.put("year",bean.getYear());
        values.put("monty",bean.getMonty());
        values.put("day",bean.getDay());
        values.put("kind",bean.getKind());
        db.insert("accounttb",null,values);
        Log.i("animee","insertItemToAccounttb:ok!!!");
    }

    /**
     * 获取记账表中某一天的所有支出或者收入情况
     */

    public static  List<AccoountBean> getAccountListOneDayFromAccounttb(int year,int month,int day){
        List<AccoountBean> list=new ArrayList<>();
        String sql="select * from accounttb where year=? and monty=? and day=? order by id desc";
        Cursor cursor=db.rawQuery(sql,new String[]{year+"",month+"",day+""});
        //遍历符合要求的每一行数据
        while (cursor.moveToNext()) {
            int id=cursor.getInt(cursor.getColumnIndex("id"));
            String typename=cursor.getString(cursor.getColumnIndex("typename"));
            String beizhu=cursor.getString(cursor.getColumnIndex("beizhu"));
            String time=cursor.getString(cursor.getColumnIndex("time"));
            int sImageId=cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind=cursor.getInt(cursor.getColumnIndex("kind"));
            Float money=cursor.getFloat(cursor.getColumnIndex("money"));
            AccoountBean accoountBean=new AccoountBean(id,typename,sImageId,beizhu,money,time,year,month,day,kind);
            list.add(accoountBean);
        }
        return list;
    }

    /**
     * 获取记账表中某一天的所有支出或者收入情况
     */

    public static  List<AccoountBean> getAccountListOneMonthFromAccounttb(int year,int month){
        List<AccoountBean> list=new ArrayList<>();
        String sql="select * from accounttb where year=? and monty=? order by id desc";
        Cursor cursor=db.rawQuery(sql,new String[]{year+"",month+""});
        //遍历符合要求的每一行数据
        while (cursor.moveToNext()) {
            int id=cursor.getInt(cursor.getColumnIndex("id"));
            String typename=cursor.getString(cursor.getColumnIndex("typename"));
            String beizhu=cursor.getString(cursor.getColumnIndex("beizhu"));
            String time=cursor.getString(cursor.getColumnIndex("time"));
            int sImageId=cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind=cursor.getInt(cursor.getColumnIndex("kind"));
            Float money=cursor.getFloat(cursor.getColumnIndex("money"));
            int day=cursor.getInt(cursor.getColumnIndex("day"));
            AccoountBean accoountBean=new AccoountBean(id,typename,sImageId,beizhu,money,time,year,month,day,kind);
            list.add(accoountBean);
        }
        return list;
    }
    /**
     * 获得某一天的支出或者收入的总金额  kind:支出==0，收入==1
     */
    public static float getSumMoneyOneDay(int year,int month,int day,int kind){
        float total=0.0f;
        String sql="select sum(money) from accounttb where year=? and monty=? and day=? and kind=?";
        Cursor cursor= db.rawQuery(sql,new String[]{year+"",month+"",day+"",kind+""});
        //遍历
        if (cursor.moveToNext()) {
            float money=cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total=money;
        }
        return total;
    }

    /**
     * 获得某一月的支出或者收入的总金额  kind:支出==0，收入==1
     */
    public static float getSumMoneyOneMonth(int year,int month,int kind){
        float total=0.0f;
        String sql="select sum(money) from accounttb where year=? and monty=? and kind=?";
        Cursor cursor= db.rawQuery(sql,new String[]{year+"",month+"",kind+""});
        //遍历
        if (cursor.moveToNext()) {
            float money=cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total=money;
        }
        return total;
    }
    /**
     * 获得某一年的支出或者收入的总金额  kind:支出==0，收入==1
     */
    public static float getSumMoneyOneYear(int year,int kind){
        float total=0.0f;
        String sql="select sum(money) from accounttb where year=? and kind=?";
        Cursor cursor= db.rawQuery(sql,new String[]{year+"",kind+""});
        //遍历
        if (cursor.moveToNext()) {
            float money=cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total=money;
        }
        return total;
    }
    /**
     * 统计某月份支出或者收入情况有多少条，收入-1，支出-0
     */
    public static int getCountItemOneMonth(int year,int month,int kind){
        int total=0;
        String sql="select count(id) from accounttb where year=? and monty=? and kind=?";
        Cursor cursor=db.rawQuery(sql,new String[]{year+"",month+"",kind+""});
        if (cursor.moveToFirst()) {
            int count=cursor.getInt(cursor.getColumnIndex("count(id)"));
            total=count;
        }

        return total;
    }

    /**
     * 根据传入的id,删除accounttb表当中的一条数据
     */
    public static int deleteItemFromAccounttbById(int id){
        int i= db.delete("accounttb","id=?",new String[]{id+""});
        return i;
    }

    /**
     * 根据备注搜索收入或者支出的情况列表
     */
    public static List<AccoountBean> getAccountListByRemarkFromAccounttb(String beizhu){
        List<AccoountBean> list=new ArrayList<>();
        String sql="select * from accounttb where beizhu like '%"+beizhu+"%'";
        Cursor cursor=db.rawQuery(sql,null);
        while (cursor.moveToNext()) {
            int id=cursor.getInt(cursor.getColumnIndex("id"));
            String typename=cursor.getString(cursor.getColumnIndex("typename"));
            String bz=cursor.getString(cursor.getColumnIndex("beizhu"));
            String time=cursor.getString(cursor.getColumnIndex("time"));
            int sImageId=cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind=cursor.getInt(cursor.getColumnIndex("kind"));
            Float money=cursor.getFloat(cursor.getColumnIndex("money"));
            int year=cursor.getInt(cursor.getColumnIndex("year"));
            int month=cursor.getInt(cursor.getColumnIndex("monty"));
            int day=cursor.getInt(cursor.getColumnIndex("day"));
            AccoountBean accoountBean=new AccoountBean(id,typename,sImageId,bz,money,time,year,month,day,kind);
            list.add(accoountBean);
        }
        return list;
    }
    /**
     * 查询记账的表当中有几个年份信息
     */
    public static List<Integer> getYearListFromAccounttb(){
        List<Integer> list=new ArrayList<>();
        String sql="select distinct(year) from accounttb order by year asc";
        Cursor cursor=db.rawQuery(sql,null);
        while (cursor.moveToNext()) {
            int year=cursor.getInt(cursor.getColumnIndex("year"));
            list.add(year);
        }
        return list;
    }
    /**
     * 删除accounttb表格中的所有数据
     */
    public static void deleteAccount(){
        String sql="delete from accounttb";
        db.execSQL(sql);
    }
    /**
     * 查询指定年份或者月份的收入或者支出的每一种类型的总钱数
     *
     */
    public static List<ChartItemBean> getChartListFromAccounttb(int year,int month,int kind){
        List<ChartItemBean> list=new ArrayList<>();
        float sumMoneyOneMonth=getSumMoneyOneMonth(year,month,kind);//先求出支出或者收入总钱数
        String sql="select typename,sImageId,sum(money)as total from accounttb where year=? and monty=? and kind=? group by typename "+
                "order by total desc";
        Cursor cursor=db.rawQuery(sql,new String[]{year+"",month+"",kind+""});
       while (cursor.moveToNext()){
           int sImageId=cursor.getInt(cursor.getColumnIndex("sImageId"));
           String typename=cursor.getString(cursor.getColumnIndex("typename"));
           float total=cursor.getFloat(cursor.getColumnIndex("total"));
            //计算所占百分比，total/sumMonth
            float ratio= FloatUtils.div(total,sumMoneyOneMonth);
            ChartItemBean bean=new ChartItemBean(sImageId,typename,ratio,total);
            list.add(bean);
       }
        return list;
    }

    /**
     * 获取这个月某一天当中收入，支出最大的金额
     */
    public static float getMaxMoneyOneDayInMonth(int year,int month,int kind){
        String sql="select sum(money) from accounttb where year=? and monty=? and kind=? group by day order by sum(money) desc";
        Cursor cursor= db.rawQuery(sql,new String[]{year+"",month+"",kind+""});
        if (cursor.moveToFirst()){
            Float money=cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            return money;
        }
        return 0;
    }
    /**
     * 根据指定月份获取每日收入或者支出总钱数的集合
     */
    public static List<BarChartItemBean> getSumMoneyOneDayInMonth(int year,int month,int kind){
        String sql="select day,sum(money) from accounttb where year=? and monty=? and kind=? group by day";
        Cursor cursor=db.rawQuery(sql,new String[]{year+"",month+"",kind+""});
        List<BarChartItemBean> list=new ArrayList<>();
        while (cursor.moveToFirst()) {
            int day=cursor.getInt(cursor.getColumnIndex("day"));
            float smoney=cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            BarChartItemBean itemBean= new BarChartItemBean(year,month,day,smoney);
            list.add(itemBean);
        }
        return list;
    }
}
